﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;

namespace BankRemoteSystem.DAL
{
    public class Connection
    {
        private string strConnect;
        private SqlConnection cnn;
        private SqlCommand cmd;
        private SqlDataAdapter adp;
        public string StrConnect
        {
            get { return strConnect; }
            set { strConnect = value; }
        }

        public Connection()
        {
            strConnect = "Data Source=Nice-world;Database=Bank;User ID = sa;Password = 123";
        }

        public Connection(string connectionString)
        {
            strConnect = connectionString;
        }

        public SqlConnection getConnection()
        {
            if (cnn == null)
            {
                cnn = new SqlConnection(strConnect);
                cmd = cnn.CreateCommand();
                cnn.Open();
            }
            return cnn;
        }

        public void closeConnection()
        {
            cnn.Close();
        }

        public int executeSQL(string sql)
        {
            if (cnn == null)
            {
                cnn = new SqlConnection(strConnect);
                cnn.Open();
            }
            SqlCommand cmd = new SqlCommand(sql, cnn);
            int num = cmd.ExecuteNonQuery();
            cmd.Dispose();
            cmd = null;
            cnn.Close();
            cnn = null;
            return num;
        }
        public DataSet ExecuteQueryDataSet(string strSQL, CommandType ct, List<SqlParameter> paramList,string name)
        {
            if (cnn == null)
            {
                cnn = new SqlConnection(strConnect);
                cmd = cnn.CreateCommand();
                cnn.Open();
            }                
            cmd.Parameters.Clear();
            cmd.CommandText = strSQL;
            cmd.CommandType = ct;
            if (paramList != null)
                foreach (SqlParameter p in paramList)
                    cmd.Parameters.Add(p);
            adp = new SqlDataAdapter(cmd);
            DataSet ds = new DataSet();
            adp.Fill(ds,name);                
            return ds;
        }
        public SqlDataReader executeQuery(string strSQL, CommandType ct, List<SqlParameter> paramList)
        {
            if (cnn == null)
            {
                cnn = new SqlConnection(strConnect);
                cmd = cnn.CreateCommand();
                cnn.Open();
            }

            cmd.Parameters.Clear();
            cmd.CommandText = strSQL;
            cmd.CommandType = ct;
            if (paramList != null)
                foreach (SqlParameter p in paramList)
                    cmd.Parameters.Add(p);
            try
            {
                return cmd.ExecuteReader();

            }
            catch (SqlException ex)
            {
                Console.Write("error" + ex);
                return null;
            }

        }
        public SqlDataReader executeQuery(String sql)
        {
            if (cnn == null)
            {
                cnn = new SqlConnection(strConnect);
                cnn.Open();
            }
            SqlCommand cmd = new SqlCommand(sql, cnn);
            return cmd.ExecuteReader();               
        }

        public bool MyExecuteNonQuery(string strSQL, CommandType ct, List<SqlParameter> paramList)
        {
            bool f = false;
            if (cnn == null)
            {
                cnn = new SqlConnection(strConnect);
                cmd = cnn.CreateCommand();
                cnn.Open();
            }
            cmd.Parameters.Clear();
            cmd.CommandText = strSQL;
            cmd.CommandType = ct;
            if (paramList != null)
                foreach (SqlParameter p in paramList)
                    cmd.Parameters.Add(p);
            try
            {
                cmd.ExecuteNonQuery();
                f = true;
            }
            catch (SqlException ex)
            {
                Console.Write(ex);
            }

            return f;
        }
    }    
}
